namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageCustomerSchool
    {
        public static SqlDataReader GetCustomerSchool()
        {
            string strSQL = "Select * from TB_CustomerSchool order by SchoolName ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerSchool GetCustomerSchoolViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            CustomerSchool customerSchool = new CustomerSchool();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerSchool where SchoolId = @SchoolId";
            MyDB.AddParameters(id, "SchoolId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerSchool.SchoolId = MyReader.GetGuid(0);
                customerSchool.SchoolName = MyReader.GetString(1);
                customerSchool.SchoolEnName = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return customerSchool;
        }

        public static void InsertNewSchool(string name, string nameEn)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "SchoolName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "SchoolEnName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "SchoolId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_CustomerSchool (SchoolName,SchoolEnName,SchoolId) values (@SchoolName,@SchoolEnName,@SchoolId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistDefInOtherTable(Guid defId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(defId, "CustomerDefId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerBasicInfo where CustomerDefId = @CustomerDefId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            return false;
        }

        public static bool IsExistSameSchoolName(string name)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "SchoolName", SqlDbType.VarChar);
            string strSQL = "select * from TB_CustomerSchool where SchoolName = @SchoolName";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveSchoolViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "SchoolId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerSchool where SchoolId = @SchoolId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateSchool(string name, string nameEn, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "SchoolName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "SchoolEnName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "SchoolId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerSchool set SchoolName = @SchoolName,SchoolEnName=@SchoolEnName where SchoolId = @SchoolId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

